package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.UriageShubetsuBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.RaitenYoinModel;
import jp.bsws.model.TenpoRelationModel;
import jp.bsws.model.UriageShubetsuModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class UriageShubetsuDao {
	private static UriageShubetsuDao uriageShubetsuDao = new UriageShubetsuDao();

	/** ログ */
	private Log log = LogFactory.getLog(UriageShubetsuDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static UriageShubetsuDao getInstance(){
		return uriageShubetsuDao;
	}

	private String SQL_SELECT_URIAGE_SHUBETSU = " SELECT * FROM M_URIAGE_SHUBETSU WHERE GROUP_COD = ? ";

	/**
	 * 売上種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<UriageShubetsuModel> getUriageShubetsuJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod) throws Exception{

		List<UriageShubetsuModel> resultList = new ArrayList<UriageShubetsuModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq =  " AND SEQ = ? ";
		}

		String whereTenpo = "";
		//店舗で抽出
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			TenpoRelationDao tenpoRelationDao = TenpoRelationDao.getInstance();
			List<TenpoRelationModel> tmpList = tenpoRelationDao.getTenpoRelationInfo(con, pkGroupCod, pkTenpoCod, String.valueOf(BSWSConstants.KBN_URIAGE_SHUBETSU));
			whereTenpo = tenpoRelationDao.makeSeqList(tmpList);
		}

		String orderBy = " ORDER BY URIAGE_SHUBETSU_HYOJI_JUN";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_URIAGE_SHUBETSU + whereSeq + whereTenpo + orderBy);
		stmt.setString(1, pkGroupCod);
		if( pkSeq != null ){
			stmt.setInt(2, pkSeq);
		}

		ResultSet rs = stmt.executeQuery();
		UriageShubetsuModel uriageShubetsuModel = null;
		while(rs.next()){
			uriageShubetsuModel = new UriageShubetsuModel();
			uriageShubetsuModel.setSeq( rs.getInt("SEQ"));
			uriageShubetsuModel.setStrGroupCod(rs.getString("GROUP_COD"));
			uriageShubetsuModel.setStrUriageShubetsuKnj(rs.getString("URIAGE_SHUBETSU_KNJ"));
			uriageShubetsuModel.setStrUriageShubetsuRyakuKnj(rs.getString("URIAGE_SHUBETSU_RYAKU_KNJ"));
			uriageShubetsuModel.setIntUriageShubetsuHyojiJun(rs.getInt("URIAGE_SHUBETSU_HYOJI_JUN"));
			uriageShubetsuModel.setStrUriageShubetsuHyojiFlg(rs.getString("URIAGE_SHUBETSU_HYOJI_FLG"));
			resultList.add(uriageShubetsuModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_URIAGE_SHUBETSU_INSERT =
		" INSERT INTO " +
		"     M_URIAGE_SHUBETSU ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,URIAGE_SHUBETSU_KNJ " +
		"        ,URIAGE_SHUBETSU_RYAKU_KNJ " +
		"        ,URIAGE_SHUBETSU_HYOJI_JUN " +
		"        ,URIAGE_SHUBETSU_HYOJI_FLG " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 売上種別情報追加
	 */
	public int insertUriageShubetsu(Connection con, Integer seq, BSWSSession bSWSSession , UriageShubetsuBean uriageShubetsuBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_URIAGE_SHUBETSU");
		if(seq == null){
			seq = maxSeq + 1;
		}

		PreparedStatement stmt = con.prepareStatement(SQL_URIAGE_SHUBETSU_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setString(3, uriageShubetsuBean.getStrUriageShubetsuKnj());
		stmt.setString(4, uriageShubetsuBean.getStrUriageShubetsuRyakuKnj());
		stmt.setInt(5, Integer.parseInt(uriageShubetsuBean.getStrUriageShubetsuHyojiJun()));
		stmt.setInt(6, Integer.parseInt(uriageShubetsuBean.getStrUriageShubetsuHyojiFlg()));
		stmt.setString(7, bSWSSession.getStrJugyoinCod());
		stmt.setString(8, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_URIAGE_SHUBETSU_UPDATE =
		" UPDATE " +
		"     M_URIAGE_SHUBETSU " +
		" SET " +
		"         URIAGE_SHUBETSU_KNJ = ? " +
		"        ,URIAGE_SHUBETSU_RYAKU_KNJ = ? " +
		"        ,URIAGE_SHUBETSU_HYOJI_JUN = ? " +
		"        ,URIAGE_SHUBETSU_HYOJI_FLG = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 売上種別情報更新
	 */
	public int updateUriageShubetsu(Connection con,BSWSSession bSWSSession , UriageShubetsuBean uriageShubetsuBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_URIAGE_SHUBETSU_UPDATE);

		stmt.setString(1, uriageShubetsuBean.getStrUriageShubetsuKnj());
		stmt.setString(2, uriageShubetsuBean.getStrUriageShubetsuRyakuKnj());
		stmt.setInt(3, Integer.parseInt(uriageShubetsuBean.getStrUriageShubetsuHyojiJun()));
		stmt.setString(4, uriageShubetsuBean.getStrUriageShubetsuHyojiFlg());
		stmt.setString(5, bSWSSession.getStrJugyoinCod());
		stmt.setInt(6, uriageShubetsuBean.getSeq());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KV = " SELECT SEQ,URIAGE_SHUBETSU_KNJ FROM M_URIAGE_SHUBETSU WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている売上種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getUriageShubetsuList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KV + where);

		stmt.setString(1, pkGroupCod);
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			stmt.setString(2, pkTenpoCod);
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("SEQ");
			String value = rs.getString("URIAGE_SHUBETSU_KNJ");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}
}